NumericFunctions.Regex position function

Returns the position a pattern occurs in another string

Format

ANSI-SQL Format:

POSITION_REGEX( [ { START | AFTER } ] Pattern, [ FLAG Options ] IN Subject [ FROM Start ] [ OCCURRENCE Occurence ] )

Remarks

Returns the 1-based offset of the Pattern in Subject. Returns zero if the Pattern cannot be found in Subject.

The flags START and AFTER control whether the position of the Pattern is returned or the position after the Pattern is returned. If neither START or AFTER are specified, START is assumed, which returns the position of the Pattern.

Pattern is the regular expression string to search for. For more information on regular expression patterns, refer to Regular Expressions.

If specified, the Option argument changes the pattern matching behaviour. For more information on regular expression options refer to Regular Expression Option Flags

Subject is the string to be searched.

If specified, the Start argument is the 1-based offset into Subject to start the search. If Start is less than 1 or greater than the length of Subject, the function returns 0. If Start is not specified, the starting position is assumed to be 1, which is the first character in Subject

If specified, the Occurrence argument defines which occurrence of the Pattern to return. If Occurrence is 1, the position of the 1st occurrence of Pattern is returned. If Occurrence is 2, the position of the 2nd occurrence of Pattern is returned. If Occurrence is less than 1 or greater than the number of occurrences of Pattern, the function returns 0. If Occurrence is not specified, the value 1 is assumed, which returns the position of the first occurrence.